package model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import db.DBUtil;
import db.DbException;

public class UniversityModel {

	private static String selectUniversity = "SELECT name FROM Universities WHERE location = ?";
	private static String selectLocation = "SELECT DISTINCT location FROM Universities";
	
	public static Vector<String> getAllUniversity(String location) throws DbException {
		try {
			// Create the statement
			PreparedStatement pStmt = DBUtil.prepareStatement(selectUniversity);
			pStmt.setString(1, location);

			ResultSet rs = pStmt.executeQuery();

			Vector<String> universities = new Vector<String>();
			while (rs.next()) {
				universities.add(rs.getString(2));
			}

			rs.close();
			pStmt.close();
			DBUtil.close();

			return universities;
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}
	
	public static Vector<String> getAllLocation() throws DbException{
		try {
			// Create the statement
			Statement stmt = DBUtil.createStatement();

			ResultSet rs = stmt.executeQuery(selectLocation);

			Vector<String> locations = new Vector<String>();
			while (rs.next()) {
				locations.add(rs.getString(2));
			}

			rs.close();
			stmt.close();
			DBUtil.close();

			return locations;
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}
}
